package dao.impl.StoreDaoImpl;

import common.model.Entity.StoreEntity.Order;
import dao.StoreDao.OrderDao;
import common.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * 订单数据访问接口实现类，提供订单相关的数据库操作实现
 */
public class OrderDaoImpl implements OrderDao {
    private static final Logger logger = LoggerFactory.getLogger(OrderDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();

    @Override
    public Optional<Order> findById(Long id) {
        String sql = "SELECT id, order_number AS orderNumber, user_id AS userId, " +
                "total_amount AS totalAmount, status, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            Order order = queryRunner.query(conn, sql, new BeanHandler<>(Order.class), id);
            return Optional.ofNullable(order);
        } catch (SQLException e) {
            logger.error("根据ID查询订单失败: id={}", id, e);
            return Optional.empty();
        }
    }

    @Override
    public Optional<Order> findByOrderNumber(String orderNumber) {
        String sql = "SELECT id, order_number AS orderNumber, user_id AS userId, " +
                "total_amount AS totalAmount, status, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order WHERE order_number = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            Order order = queryRunner.query(conn, sql, new BeanHandler<>(Order.class), orderNumber);
            return Optional.ofNullable(order);
        } catch (SQLException e) {
            logger.error("根据订单号查询订单失败: orderNumber={}", orderNumber, e);
            return Optional.empty();
        }
    }

    @Override
    public List<Order> findByUser(Long userId) {
        String sql = "SELECT id, order_number AS orderNumber, user_id AS userId, " +
                "total_amount AS totalAmount, status, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order WHERE user_id = ? AND is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Order.class), userId);
        } catch (SQLException e) {
            logger.error("根据用户ID查询订单失败: userId={}", userId, e);
            return List.of();
        }
    }

    @Override
    public List<Order> findByStatus(int status) {
        String sql = "SELECT id, order_number AS orderNumber, user_id AS userId, " +
                "total_amount AS totalAmount, status, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order WHERE status = ? AND is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Order.class), status);
        } catch (SQLException e) {
            logger.error("根据状态查询订单失败: status={}", status, e);
            return List.of();
        }
    }

    @Override
    public List<Order> findByUserAndStatus(Long userId, int status) {
        String sql = "SELECT id, order_number AS orderNumber, user_id AS userId, " +
                "total_amount AS totalAmount, status, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order WHERE user_id = ? AND status = ? AND is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Order.class), userId, status);
        } catch (SQLException e) {
            logger.error("根据用户ID和状态查询订单失败: userId={}, status={}", userId, status, e);
            return List.of();
        }
    }

    @Override
    public List<Order> findAll() {
        String sql = "SELECT id, order_number AS orderNumber, user_id AS userId, " +
                "total_amount AS totalAmount, status, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order WHERE is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Order.class));
        } catch (SQLException e) {
            logger.error("查询所有订单失败", e);
            return List.of();
        }
    }

    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_order SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, id);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("删除订单失败: id={}", id, e);
            return false;
        }
    }

    @Override
    public boolean save(Order order) {
        String sql = "INSERT INTO t_order (order_number, user_id, total_amount, status) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection()) {
            Number key = queryRunner.insert(conn, sql, new ScalarHandler<Number>(),
                    order.getOrderNumber(),
                    order.getUserId(),
                    order.getTotalAmount(),
                    order.getStatus());
            order.setId(key.longValue()); // 安全转成 long
            return true;
        } catch (SQLException e) {
            logger.error("保存订单失败: orderNumber={}", order.getOrderNumber(), e);
            return false;
        }
    }

    @Override
    public boolean update(Order order) {
        String sql = "UPDATE t_order SET total_amount = ?, status = ?, updated_at = CURRENT_TIMESTAMP " +
                "WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql,
                    order.getTotalAmount(),
                    order.getStatus(),
                    order.getId());
            return affected > 0;
        } catch (SQLException e) {
            logger.error("更新订单失败: id={}", order.getId(), e);
            return false;
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_order WHERE is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计订单数量失败", e);
            return 0;
        }
    }
}